Staging Database:

Create 3 staging tables under staging database.

PhysicalInspection_StgMaster table.

PhsicalInspection_Stg1 table and PhsicalInspection_Stg2 table

SSIS Project:

SSIS project named “DWH_ProjectPhase1” created for this project and the first package is to load from the JSON file to Staging.

Developing SSIS Package to load the JSON file to staging1(PhsicalInspection_Stg1table )

Execute SQL Task used to truncate the Staging table every time the package executes.

Developing Custom Data Source for JSON File under the SSIS Package.
As Microsoft has not provided default data source for JSON files, we must develop custom data source using Script Component.

Steps to develop custom data source using Script Component:

- Add Script Component to the Data Flow Task and select Source.

Then Under Inputs and Outputs > Output Columns we need to add field names of the JSON file. The data types for all the fields set to String as JSON Serializer cannot handle null INT values.

After all the output attributes defined as above, we are ready to the develop the custom code using Visual Studio C# or Visual Basic. For my purpose I choose C#.
To bring VS editor, Edit Script need to be clicked Under Script.

VS Studio Editor shows up as below. We will be using the function in the .Net library System.Web.Extension to read the JSON file. To access the function, we need to add this library as a reference. This can be achieved by selecting the project and by right clicking on Add Reference as shown below.

Under .Net Framework, select System.Web.Extension and Click OK.

Once done, the library will be visible under the reference section.

Then we need to create a class to hold the JSON content. A C# class can be created by selecting the project and click Add > Class as mentioned in the image below and select Class from Add New Item Dialogue box and give a name for the Class (“PhsicalInspection”), Images below.

Then we need to select the Class from the solutions explorer and rename the default namespace (for this package ‘InspectionNamespace’). And then we need to define the class data types the same as the attributes in JSON file as below.

Then we need to switch to main.cs as below. At this step we need to include the name space defined in the ‘PhiscalInspection’ class in the main.cs as below (‘Using InspectionNamespace’) and then under public override void CreateNewOutputRows() we need to provide the JSON file path and create an instance of the object JavaScriptSerializer and use the "Deserialize" function in the JavaScriptSerializer object to deserialize and return a runtime object defined (‘PhiscalInspection’).

Note: Deserialization is a process that helps to transform JSON document to a runtime object. Once the data is available as a runtime object, then it can be parsed by using the .Net libraries. Now we need to read the JSON file content and deserialize it to convert into runtime object. The JavaScriptSeralizaer is an object defined in the System.Web.extension assembly that will be used to deserialize the JSON file content.

At this point the Script Component setup and custom C# code for the JSON file completed and need to be closed. Next, applying necessary transformations and loading to the Staging table.

Then the destination component (OLE DB Destination) setup and mapping applied.

Execute the package.

Retrieving records from management studio staging table(PhasicalInspection_Stg1).

Develop SSIS package to load two individual staging tables into one master staging table.

Use Merge Join Transformation.

Derived Column transformation used to replace unwanted characters. Created and give value to those Derived columns (LoadDate, LoadedBy, UpdateDate,ModifiedBy)

Next Conditional Split applied to remove Blank values for the required fields.

Then the destination component (OLE DB Destination) setup and mapping applied.

Last Execution

Retrieving records from management studio staging table.